In [42]:
from IPython.core.display import display, HTML
display(HTML("""<style> .container {width:96% !important;}</style>"""))

from IPython.display import IFrame
https://amstat.tandfonline.com/doi/full/10.1080/10691898.2018.1434342#.W4TAIi2B00ofrom __future__ import print_function from future import division
In [25]:
import pandas as pd
import numpy as np
from plotly.offline import init_notebook_mode, iplot
import cufflinks as cf
init_notebook_mode()
cf.go_offline()
In [2]:
path_data = '../large_data_files/ASA_loan_data/'
In [18]:
df = pd.read_csv(path_data + 'SBAnational.csv', low_memory=False)
print df.shape
df = df[df.ApprovalFY != '1976A']
df['ApprovalFY'] = df.ApprovalFY.astype(int)
df = df[(df.ApprovalFY >= 1990) & (pd.notnull(df.MIS_Status))]
print df.shape

(899164, 27)
(869288, 27)
In [19]:
df.head()
Out[19]:
LoanNr_ChkDgt Name City State Zip Bank BankState NAICS ApprovalDate ApprovalFY ... RevLineCr LowDoc ChgOffDate DisbursementDate DisbursementGross BalanceGross MIS_Status ChgOffPrinGr GrAppv SBA_Appv
0 1000014003 ABC HOBBYCRAFT EVANSVILLE IN 47711 FIFTH THIRD BANK OH 451120 28-Feb-97 1997 ... N Y NaN 28-Feb-99 $60,000.00 $0.00 P I F $0.00 $60,000.00 $48,000.00
1 1000024006 LANDMARK BAR & GRILLE (THE) NEW PARIS IN 46526 1ST SOURCE BANK IN 722410 28-Feb-97 1997 ... N Y NaN 31-May-97 $40,000.00 $0.00 P I F $0.00 $40,000.00 $32,000.00
2 1000034009 WHITLOCK DDS, TODD M. BLOOMINGTON IN 47401 GRANT COUNTY STATE BANK IN 621210 28-Feb-97 1997 ... N N NaN 31-Dec-97 $287,000.00 $0.00 P I F $0.00 $287,000.00 $215,250.00
3 1000044001 BIG BUCKS PAWN & JEWELRY, LLC BROKEN ARROW OK 74012 1ST NATL BK & TR CO OF BROKEN OK 0 28-Feb-97 1997 ... N Y NaN 30-Jun-97 $35,000.00 $0.00 P I F $0.00 $35,000.00 $28,000.00
4 1000054004 ANASTASIA CONFECTIONS, INC. ORLANDO FL 32801 FLORIDA BUS. DEVEL CORP FL 0 28-Feb-97 1997 ... N N NaN 14-May-97 $229,000.00 $0.00 P I F $0.00 $229,000.00 $229,000.00

5 rows × 27 columns

In [31]:
def to_float(x):
    x = x.replace('$', '')
    x = x.replace(',', '')
    x = float(x)
    return x
In [40]:
df['ChgOffPrinGr'] = df['ChgOffPrinGr'].apply(to_float)
df['GrAppv'] = df['GrAppv'].apply(to_float)
df['SBA_Appv'] = df['SBA_Appv'].apply(to_float)
In [41]:
df.head()
Out[41]:
LoanNr_ChkDgt Name City State Zip Bank BankState NAICS ApprovalDate ApprovalFY ... RevLineCr LowDoc ChgOffDate DisbursementDate DisbursementGross BalanceGross MIS_Status ChgOffPrinGr GrAppv SBA_Appv
0 1000014003 ABC HOBBYCRAFT EVANSVILLE IN 47711 FIFTH THIRD BANK OH 451120 28-Feb-97 1997 ... N Y NaN 28-Feb-99 $60,000.00 $0.00 P I F 0.0 60000.0 48000.0
1 1000024006 LANDMARK BAR & GRILLE (THE) NEW PARIS IN 46526 1ST SOURCE BANK IN 722410 28-Feb-97 1997 ... N Y NaN 31-May-97 $40,000.00 $0.00 P I F 0.0 40000.0 32000.0
2 1000034009 WHITLOCK DDS, TODD M. BLOOMINGTON IN 47401 GRANT COUNTY STATE BANK IN 621210 28-Feb-97 1997 ... N N NaN 31-Dec-97 $287,000.00 $0.00 P I F 0.0 287000.0 215250.0
3 1000044001 BIG BUCKS PAWN & JEWELRY, LLC BROKEN ARROW OK 74012 1ST NATL BK & TR CO OF BROKEN OK 0 28-Feb-97 1997 ... N Y NaN 30-Jun-97 $35,000.00 $0.00 P I F 0.0 35000.0 28000.0
4 1000054004 ANASTASIA CONFECTIONS, INC. ORLANDO FL 32801 FLORIDA BUS. DEVEL CORP FL 0 28-Feb-97 1997 ... N N NaN 14-May-97 $229,000.00 $0.00 P I F 0.0 229000.0 229000.0

5 rows × 27 columns

In [39]:
default.head()
Out[39]:
LoanNr_ChkDgt Name City State Zip Bank BankState NAICS ApprovalDate ApprovalFY ... RevLineCr LowDoc ChgOffDate DisbursementDate DisbursementGross BalanceGross MIS_Status ChgOffPrinGr GrAppv SBA_Appv
36 1000554001 PAUL E. & JUDY A. FAATZ KINSMAN OH 44428 CORTLAND SAVINGS & BK. CO. OH 0 28-Feb-97 1997 ... N Y 18-Apr-02 30-Jun-97 $47,000.00 $0.00 CHGOFF $14,084.00 $47,000.00 37600.0
58 1000726007 NEW DIMENSION HOMES INC CLINTON (CENSUS NAME FOR CLINT ME 4927 WELLS FARGO BANK NATL ASSOC SD 236115 7-Feb-06 2006 ... 0 N 26-Jun-14 28-Feb-06 $1,350,000.00 $0.00 CHGOFF $1,043,508.00 $1,350,000.00 1012500.0
60 1000735003 Mykols Marine Inc. Spring Lake MI 49417 WELLS FARGO BANK NATL ASSOC SD 713930 31-Mar-97 1997 ... 0 N 4-Oct-05 28-Feb-98 $25,000.00 $0.00 CHGOFF $23,484.00 $25,000.00 12500.0
64 1000755009 Grass Valley Market Grass Valley OR 0 WELLS FARGO BANK NATL ASSOC SD 0 31-Mar-97 1997 ... 0 N 1-Mar-02 28-Feb-98 $20,000.00 $0.00 CHGOFF $19,653.00 $20,000.00 10000.0
67 1000766008 LILY DAY GARDENS BURNET TX 78611 WELLS FARGO BANK NATL ASSOC SD 444220 7-Feb-06 2006 ... Y N 27-Jan-11 28-Feb-06 $60,859.00 $0.00 CHGOFF $19,994.00 $20,000.00 10000.0

5 rows × 27 columns

In [20]:
df.MIS_Status.value_counts()
Out[20]:
P I F     719521
CHGOFF    149767
Name: MIS_Status, dtype: int64
In [21]:
df.ApprovalFY.value_counts().sort_index()
Out[21]:
1990    14859
1991    15660
1992    20875
1993    23299
1994    31584
1995    45688
1996    40021
1997    37718
1998    36005
1999    37348
2000    37352
2001    37317
2002    44307
2003    58000
2004    68195
2005    76958
2006    75756
2007    71649
2008    39458
2009    19103
2010    16828
2011    12593
2012     5992
2013     2455
2014      268
Name: ApprovalFY, dtype: int64
In [29]:
df.groupby('ApprovalFY').count().max(1).iplot(kind = 'bar', title = 'Loan cases')
In [27]:
(df[df.MIS_Status == 'CHGOFF'].groupby('ApprovalFY').count().max(1) / 
 df.groupby('ApprovalFY').count().max(1)).iplot(kind = 'bar', title = 'Default rate')
In [36]:
default = df[df.MIS_Status == 'CHGOFF'].copy()

default.groupby('ApprovalFY').SBA_Appv.sum().iplot(kind = 'bar')
In [33]:
default.head()
Out[33]:
LoanNr_ChkDgt Name City State Zip Bank BankState NAICS ApprovalDate ApprovalFY ... RevLineCr LowDoc ChgOffDate DisbursementDate DisbursementGross BalanceGross MIS_Status ChgOffPrinGr GrAppv SBA_Appv
36 1000554001 PAUL E. & JUDY A. FAATZ KINSMAN OH 44428 CORTLAND SAVINGS & BK. CO. OH 0 28-Feb-97 1997 ... N Y 18-Apr-02 30-Jun-97 $47,000.00 $0.00 CHGOFF $14,084.00 $47,000.00 37600.0
58 1000726007 NEW DIMENSION HOMES INC CLINTON (CENSUS NAME FOR CLINT ME 4927 WELLS FARGO BANK NATL ASSOC SD 236115 7-Feb-06 2006 ... 0 N 26-Jun-14 28-Feb-06 $1,350,000.00 $0.00 CHGOFF $1,043,508.00 $1,350,000.00 1012500.0
60 1000735003 Mykols Marine Inc. Spring Lake MI 49417 WELLS FARGO BANK NATL ASSOC SD 713930 31-Mar-97 1997 ... 0 N 4-Oct-05 28-Feb-98 $25,000.00 $0.00 CHGOFF $23,484.00 $25,000.00 12500.0
64 1000755009 Grass Valley Market Grass Valley OR 0 WELLS FARGO BANK NATL ASSOC SD 0 31-Mar-97 1997 ... 0 N 1-Mar-02 28-Feb-98 $20,000.00 $0.00 CHGOFF $19,653.00 $20,000.00 10000.0
67 1000766008 LILY DAY GARDENS BURNET TX 78611 WELLS FARGO BANK NATL ASSOC SD 444220 7-Feb-06 2006 ... Y N 27-Jan-11 28-Feb-06 $60,859.00 $0.00 CHGOFF $19,994.00 $20,000.00 10000.0

5 rows × 27 columns

In [11]:
case = pd.read_csv(path_data + 'SBAcase_11_13_17.csv', low_memory=False)
In [13]:
print(df.shape, 
     case.shape)

(899164, 27) (2102, 35)
In [14]:
df.head()
Out[14]:
LoanNr_ChkDgt Name City State Zip Bank BankState NAICS ApprovalDate ApprovalFY ... RevLineCr LowDoc ChgOffDate DisbursementDate DisbursementGross BalanceGross MIS_Status ChgOffPrinGr GrAppv SBA_Appv
0 1000014003 ABC HOBBYCRAFT EVANSVILLE IN 47711 FIFTH THIRD BANK OH 451120 28-Feb-97 1997 ... N Y NaN 28-Feb-99 $60,000.00 $0.00 P I F $0.00 $60,000.00 $48,000.00
1 1000024006 LANDMARK BAR & GRILLE (THE) NEW PARIS IN 46526 1ST SOURCE BANK IN 722410 28-Feb-97 1997 ... N Y NaN 31-May-97 $40,000.00 $0.00 P I F $0.00 $40,000.00 $32,000.00
2 1000034009 WHITLOCK DDS, TODD M. BLOOMINGTON IN 47401 GRANT COUNTY STATE BANK IN 621210 28-Feb-97 1997 ... N N NaN 31-Dec-97 $287,000.00 $0.00 P I F $0.00 $287,000.00 $215,250.00
3 1000044001 BIG BUCKS PAWN & JEWELRY, LLC BROKEN ARROW OK 74012 1ST NATL BK & TR CO OF BROKEN OK 0 28-Feb-97 1997 ... N Y NaN 30-Jun-97 $35,000.00 $0.00 P I F $0.00 $35,000.00 $28,000.00
4 1000054004 ANASTASIA CONFECTIONS, INC. ORLANDO FL 32801 FLORIDA BUS. DEVEL CORP FL 0 28-Feb-97 1997 ... N N NaN 14-May-97 $229,000.00 $0.00 P I F $0.00 $229,000.00 $229,000.00

5 rows × 27 columns

In [15]:
case.head()
Out[15]:
Selected LoanNr_ChkDgt Name City State Zip Bank BankState NAICS ApprovalDate ... ChgOffPrinGr GrAppv SBA_Appv New RealEstate Portion Recession daysterm xx Default
0 0 1004285007 SIMPLEX OFFICE SOLUTIONS ANAHEIM CA 92801 CALIFORNIA BANK & TRUST CA 532420 15074 ... 0 30000 15000 0 0 0.5 0 1080 16175.0 0
1 1 1004535010 DREAM HOME REALTY TORRANCE CA 90505 CALIFORNIA BANK & TRUST CA 531210 15130 ... 0 30000 15000 0 0 0.5 1 1680 17658.0 0
2 0 1005005006 Winset, Inc. dba Bankers Hill SAN DIEGO CA 92103 CALIFORNIA BANK & TRUST CA 531210 15188 ... 0 30000 15000 0 0 0.5 0 1080 16298.0 0
3 1 1005535001 Shiva Management SAN DIEGO CA 92108 CALIFORNIA BANK & TRUST CA 531312 15719 ... 0 50000 25000 0 0 0.5 0 1080 16816.0 0
4 1 1005996006 GOLD CROWN HOME LOANS, INC LOS ANGELES CA 91345 SBA - EDF ENFORCEMENT ACTION CO 531390 16840 ... 0 343000 343000 0 1 1.0 0 7200 24103.0 0

5 rows × 35 columns

In [16]:
case.LoanNr_ChkDgt.isin(df.LoanNr_ChkDgt).sum()
Out[16]:
2102
In [17]:
df.LoanNr_ChkDgt.isin(case.LoanNr_ChkDgt).sum()
Out[17]:
2102
In [18]:
[col for col in case.columns if col not in df.columns]
Out[18]:
['Selected',
 'New',
 'RealEstate',
 'Portion',
 'Recession',
 'daysterm',
 'xx',
 'Default']
In [19]:
[col for col in df.columns if col not in case.columns]
Out[19]:
[]